﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Configuration;
using System.IO;
using System.Configuration;


namespace HandMade_Q4T_
{
    public class C_XLDL
    {
        /// <summary>
        /// Chuỗi kết nối database
        /// </summary>
        static string strconn = ConfigurationManager.ConnectionStrings["HANDMADE_ConnectionString"].ConnectionString.ToString();

        /// <summary>
        /// Đọc dữ liệu từ table sql
        /// </summary>
        /// <param name="lenhsql">Chuỗi truy vấn SQL</param>
        /// <returns>Trả về Table</returns>
        public DataTable BangDuLieu(string lenhsql)
        {
            using (SqlConnection sqlconn = new SqlConnection(strconn))
            {
                SqlDataAdapter da = new SqlDataAdapter(lenhsql, sqlconn);
                DataTable bang = new DataTable();
                da.Fill(bang);
                return bang;
            }
        }

        /// <summary>
        /// Thực hiện các lệnh Select, Insert, Update,....
        /// </summary>
        /// <param name="lenhsql">Lệnh sql cần thực hiện</param>
        public void ThucHienLenh(string lenhsql)
        {
            using (SqlConnection sqlconn = new SqlConnection(strconn))
            {
                sqlconn.Open();
                SqlCommand sqlcmd = new SqlCommand(lenhsql, sqlconn);
                sqlcmd.ExecuteNonQuery();
                sqlconn.Close();
            }
        }

        /// <summary>
        /// Lấy 1 chuỗi dữ liệu
        /// </summary>
        /// <param name="LenhSQL">Lệnh sql cần thực hiện</param>
        /// <returns></returns>
        public String LayDuLieu(String LenhSQL)
        {
            using (SqlConnection cnn = new SqlConnection(strconn))
            {
                cnn.Open();
                SqlCommand cmd = cnn.CreateCommand();
                cmd.CommandText = LenhSQL;
                string result = "" + cmd.ExecuteScalar().ToString();
                cnn.Close();
                return result;
            }
        }

        /// <summary>
        /// Bảng tìm kiếm
        /// </summary>
        /// <param name="LenhSQL">Lệnh sql cần truy vấn</param>
        /// <param name="thamso">Tham số đầu vào</param>
        /// <returns></returns>
        public DataTable BangTimKiem(string LenhSQL, SqlParameter[] thamso)
        {
            using (SqlConnection cnn = new SqlConnection(strconn))
            {
                SqlCommand cmd = new SqlCommand(LenhSQL, cnn);
                DataTable bang = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter();
                cnn.Open();
                da.SelectCommand = cmd;
                for (int i = 0; i < thamso.Length; i++)
                {
                    cmd.Parameters.Add(thamso[i]);
                }
                da.Fill(bang);
                cnn.Close();
                return bang;
            }
        }

        #region Load dữ liệu
        /// <summary>
        /// Load dữ liệu lên Dropdownlist
        /// </summary>
        /// <param name="ddl">Tên Dropdownlist</param>
        /// <param name="str">Lệnh sql</param>
        /// <param name="Hienthi">Trường cần hiển trị</param>
        /// <param name="Value">Value</param>
        public void Load_Dropdownlist(DropDownList ddl, string str, string Hienthi, string Value)
        {
            ddl.DataSource = BangDuLieu(str);
            ddl.DataTextField = Hienthi;
            ddl.DataValueField = Value;
            ddl.DataBind();
        }
        #endregion


        #region Xử lý chức năng
        /// <summary>
        /// Upload file (hình....)
        /// </summary>
        /// <param name="ful">Tên control</param>
        /// <param name="lenhsql">Lệnh sql/param>
        /// <param name="host">Nơi lưu trữ</param>
        public void UploadHinh(FileUpload ful, string lenhsql, string host)
        {
            string filename = ful.FileName;
            if (ful.HasFile)
            {
                if(KiemTraLoaiFile(filename))
                {
                    ThucHienLenh(lenhsql);
                    ful.SaveAs(HttpContext.Current.Server.MapPath(host + "/" + filename));
                }
            }
        }

        public bool KiemTraLoaiFile(string fileName)
        {

            string ext = Path.GetExtension(fileName);

            switch (ext.ToLower())
            {

                case ".gif":

                    return true;

                case ".png":

                    return true;

                case ".jpg":

                    return true;

                case ".jpeg":

                    return true;

                default:

                    return false;

            }

        }

        // Phân trang Scroll
        public DataSet GetCustomersData(int pageIndex, string arrange)
        {
            string query;
            if (arrange == "ASC")
            {
                query = "sp_PhanTrang̣̣̣̣";
            }
            else
            {
                query = "sp_PhanTrang̣̣̣̣_Giamdan";
            }
            SqlCommand cmd = new SqlCommand(query);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
            cmd.Parameters.AddWithValue("@PageSize", 10);
            cmd.Parameters.Add("@PageCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
            return GetData(cmd);
        }

        public DataSet GetCustomersData_Cate(int pageIndex, string tenloai, string arrange)
        {
            string query;
            if (arrange == "ASC")
            {
                query = "sp_PhanTrang̣̣̣̣_Loai";
            }
            else
            {
                query = "sp_PhanTrang̣̣̣̣_Loai_Giamdan";
            }
            
            SqlCommand cmd = new SqlCommand(query);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
            cmd.Parameters.AddWithValue("@PageSize", 10);
            cmd.Parameters.AddWithValue("@tenloai", tenloai);
            cmd.Parameters.Add("@PageCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
            return GetData(cmd);
        }

        private static DataSet GetData(SqlCommand cmd)
        {
            using (SqlConnection con = new SqlConnection(strconn))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataSet ds = new DataSet())
                    {
                        sda.Fill(ds, "SanPham");
                        DataTable dt = new DataTable("PageCount");
                        dt.Columns.Add("PageCount");
                        dt.Rows.Add();
                        dt.Rows[0][0] = cmd.Parameters["@PageCount"].Value;
                        ds.Tables.Add(dt);
                        return ds;
                    }
                }
            }
        }

        #endregion
    }
}